﻿using Dapper;
using Domain.Entity;
using Domain.Entity.bs;
using Domain.Entity.tr;
using Domain.Entity.zct;
using Domain.IRepository;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace EFCore.Repository
{
    public class Allot_hRepository : IAllot_hRepository
    {
        //连接字符串
        string con = "Data Source=121.37.93.71;Initial Catalog=CSSL_ZCTWMS;User ID=sa;Password=geili@2025;Encrypt=False";

        //查所有
        public async Task<List<Allot_h>> GetAll()
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"select * from [dbo].[tr_allot_h] where is_delete=0";
                return sqlConnection.Query<Allot_h>(sql).ToList();
            }
        }

        //查所有:分页
        public async Task<FenYe<Allot_h>> GetFenYe(FenYe<Allot_h> fenYe, string? al_code, int? al_status, int? instockoutstock_status, string? startTime, string? endTime)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string whereBuilder = $"is_delete=0 ";
                var parameters = new DynamicParameters();

                if (!string.IsNullOrEmpty(al_code))
                {
                    whereBuilder += $" and al_code like '%{al_code}%'";
                }

                if (al_status.HasValue)
                {
                    whereBuilder += $" and al_status = {al_status}";
                }

                if (instockoutstock_status.HasValue)
                {
                    whereBuilder += $" and (instock_status = {instockoutstock_status}";
                    whereBuilder += $" or outstock_status = {instockoutstock_status})";
                }

                // 修复日期处理 - 直接拼接值
                if (!string.IsNullOrEmpty(startTime) && !string.IsNullOrEmpty(endTime))
                {
                    string formattedStart = DateTime.Parse(startTime).ToString("yyyy-MM-dd HH:mm:ss");
                    string formattedEnd = DateTime.Parse(endTime).AddDays(1).AddSeconds(-1).ToString("yyyy-MM-dd HH:mm:ss");
                    whereBuilder += $" AND al_date BETWEEN '{formattedStart}' AND '{formattedEnd}'";
                }

                parameters.Add("@pageSize", 10);
                parameters.Add("@keyName", "al_id");
                parameters.Add("@tabelName", "vw_allot");
                parameters.Add("@currentPage", fenYe.CurrentPage);
                parameters.Add("@where", whereBuilder);
                parameters.Add("@rows", dbType: DbType.Int32, direction: ParameterDirection.Output);

                string sql = "exec [dbo].[Fenye] @pageSize, @keyName, @tabelName, @where, @currentPage, @rows output";
                var result = await sqlConnection.QueryAsync<Allot_h>(sql, parameters);

                fenYe.List = result.ToList();
                fenYe.Rows = parameters.Get<int>("@rows");
                return fenYe;
            }
        }

        //删除
        public async Task<int> GetDelete(int id)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $"update [dbo].[tr_allot_h] set is_delete=1 where al_id={id}";
                return await sqlConnection.ExecuteAsync(sql);
            }
        }

        //审核
        public async Task<int> GetAudit(int id,int status, string user)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $"update [dbo].[tr_allot_h] set al_status={status},approver_name='{user}',approver_time = FORMAT(GETDATE(), 'yyyy-MM-dd HH:mm:ss') where al_id={id}";
                return await sqlConnection.ExecuteAsync(sql);
            }
        }


        public async Task<FenYe<Allot_h>> GetSeniorFenYe(FenYe<Allot_h> fenYe, string? al_code, int? al_status, int[] instock_status, int[] outstock_status, string? requesttransferstartTime, string? requesttransferendTime, string? al_type, int? out_wh_id, int? in_wh_id, string? al_user,  string? create_name, string? createstartTime, string? createendTime, string? auditstartTime, string? auditendTime, string? approver_name)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string whereBuilder = "is_delete=0 ";

                // 处理字符串参数
                if (!string.IsNullOrEmpty(al_code))
                    whereBuilder += $" AND al_code LIKE '%{al_code.Replace("'", "''")}%'";

                if (al_status.HasValue)
                    whereBuilder += $" AND al_status = {al_status.Value}";

                string orCondition = "";
                if (instock_status != null && instock_status.Length > 0)
                    orCondition += $"instock_status IN ({string.Join(",", instock_status)})";

                if (outstock_status != null && outstock_status.Length > 0)
                {
                    if (orCondition.Length > 0)
                        orCondition += " OR ";
                    orCondition += $"outstock_status IN ({string.Join(",", outstock_status)})";
                }

                if (orCondition.Length > 0)
                    whereBuilder += $" AND ({orCondition})";

                // 处理其他参数
                if (!string.IsNullOrEmpty(al_type))
                    whereBuilder += $" AND al_type = '{al_type.Replace("'", "''")}'";

                if (out_wh_id.HasValue)
                    whereBuilder += $" AND out_wh_id = {out_wh_id.Value}";

                if (in_wh_id.HasValue)
                    whereBuilder += $" AND in_wh_id = {in_wh_id.Value}";

                if (!string.IsNullOrEmpty(al_user))
                    whereBuilder += $" AND al_user LIKE '%{al_user.Replace("'", "''")}%'";

                if (!string.IsNullOrEmpty(create_name))
                    whereBuilder += $" AND create_name LIKE '%{create_name.Replace("'", "''")}%'";

                if (!string.IsNullOrEmpty(approver_name))
                    whereBuilder += $" AND approver_name LIKE '%{approver_name.Replace("'", "''")}%'";

                // 处理日期范围
                if (!string.IsNullOrEmpty(requesttransferstartTime) && !string.IsNullOrEmpty(requesttransferendTime))
                {
                    var start = DateTime.Parse(requesttransferstartTime);
                    var end = DateTime.Parse(requesttransferendTime).AddDays(1).AddSeconds(-1);
                    whereBuilder += $" AND al_date BETWEEN '{start:yyyy-MM-dd HH:mm:ss}' AND '{end:yyyy-MM-dd HH:mm:ss}'";
                }

                if (!string.IsNullOrEmpty(createstartTime) && !string.IsNullOrEmpty(createendTime))
                {
                    var start = DateTime.Parse(createstartTime);
                    var end = DateTime.Parse(createendTime).AddDays(1).AddSeconds(-1);
                    whereBuilder += $" AND create_time BETWEEN '{start:yyyy-MM-dd HH:mm:ss}' AND '{end:yyyy-MM-dd HH:mm:ss}'";
                }

                if (!string.IsNullOrEmpty(auditstartTime) && !string.IsNullOrEmpty(auditendTime))
                {
                    var start = DateTime.Parse(auditstartTime);
                    var end = DateTime.Parse(auditendTime).AddDays(1).AddSeconds(-1);
                    whereBuilder += $" AND approver_time BETWEEN '{start:yyyy-MM-dd HH:mm:ss}' AND '{end:yyyy-MM-dd HH:mm:ss}'";
                }

                var parameters = new DynamicParameters();
                parameters.Add("@pageSize", 10);
                parameters.Add("@keyName", "al_id");
                parameters.Add("@tabelName", "vw_allot");
                parameters.Add("@currentPage", fenYe.CurrentPage);
                parameters.Add("@where", whereBuilder);
                parameters.Add("@rows", dbType: DbType.Int32, direction: ParameterDirection.Output);

                string sql = "exec [dbo].[Fenye] @pageSize, @keyName, @tabelName, @where, @currentPage, @rows output";
                var result = await sqlConnection.QueryAsync<Allot_h>(sql, parameters);

                fenYe.List = result.ToList();
                fenYe.Rows = parameters.Get<int>("@rows");
                return fenYe;
            }
        }

        //调拨添加
        public async Task<int> AddAudit(Allot_h allot_h, List<Allot_d> allot_ds)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                using (var transaction = sqlConnection.BeginTransaction())
                {
                    try
                    {
                        // 1. 插入调拨主表数据并获取生成的 al_id
                        var mainSql = @"
                            INSERT INTO tr_allot_h (
                                al_code,
                                al_type,
                                al_date,
                                out_wh_id,
                                out_wh_name,
                                in_wh_id,
                                in_wh_name,
                                al_user,
                                al_categories,
                                al_num,
                                al_total,
                                al_status,
                                al_outstockNO_num,
                                al_instockNO_num,
                                instock_status,
                                outstock_status,
                                create_name,
                                create_time,
                                remark
                            ) 
                            OUTPUT INSERTED.al_id
                            VALUES (
                                @al_code,
                                @al_type,
                                @al_date,
                                @out_wh_id,
                                @out_wh_name,
                                @in_wh_id,
                                @in_wh_name,
                                @al_user,
                                @al_categories,
                                @al_num,
                                @al_total,
                                @al_status,
                                @al_outstockNO_num,
                                @al_instockNO_num,
                                @instock_status,
                                @outstock_status,
                                @create_name,
                                @create_time,
                                @remark
                            );";

                        var mainParams = new DynamicParameters();
                        mainParams.Add("@al_code", allot_h.al_code);
                        mainParams.Add("@al_type", allot_h.al_type);
                        mainParams.Add("@al_date", allot_h.al_date);
                        mainParams.Add("@out_wh_id", allot_h.out_wh_id);
                        mainParams.Add("@out_wh_name", allot_h.out_wh_name);
                        mainParams.Add("@in_wh_id", allot_h.in_wh_id);
                        mainParams.Add("@in_wh_name", allot_h.in_wh_name);
                        mainParams.Add("@al_user", allot_h.al_user);
                        mainParams.Add("@al_categories", allot_h.al_categories);
                        mainParams.Add("@al_num", allot_h.al_num);
                        mainParams.Add("@al_total", allot_h.al_total);
                        mainParams.Add("@al_status", allot_h.al_status ?? 0); // 默认状态
                        mainParams.Add("@al_outstockNO_num", allot_h.al_num);
                        mainParams.Add("@al_instockNO_num", allot_h.al_num);
                        mainParams.Add("@instock_status", 0);
                        mainParams.Add("@outstock_status", 0);
                        mainParams.Add("@create_name", allot_h.create_name);
                        mainParams.Add("@create_time", allot_h.create_time ?? DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")); // 自动生成时间
                        mainParams.Add("@remark", allot_h.remark);

                        int alId = await sqlConnection.ExecuteScalarAsync<int>(
                            mainSql,
                            mainParams,
                            transaction: transaction
                        );

                        // 2. 插入调拨从表数据
                        if (allot_ds != null && allot_ds.Count > 0)
                        {
                            var detailSql = @"
                                INSERT INTO tr_allot_d (
                                    al_id,
                                    proc_id,
                                    proc_name,
                                    ald_num,
                                    ald_price,
                                    ald_total,
                                    instock_status,
                                    outstock_status,
                                    ald_outstockNO_num,
                                    ald_instockNO_num,
                                    remark
                                ) 
                                VALUES (
                                    @al_id,
                                    @proc_id,
                                    @proc_name,
                                    @ald_num,
                                    @ald_price,
                                    @ald_total,
                                    @instock_status,
                                    @outstock_status,
                                    @ald_outstockNO_num,
                                    @ald_instockNO_num,
                                    @remark
                                )";

                            foreach (var detail in allot_ds)
                            {
                                var detailParams = new DynamicParameters();
                                detailParams.Add("@al_id", alId);
                                detailParams.Add("@proc_id", detail.proc_id);
                                detailParams.Add("@proc_name", detail.proc_name);
                                detailParams.Add("@ald_num", detail.ald_num);
                                detailParams.Add("@ald_price", detail.ald_price ?? 0);
                                detailParams.Add("@ald_total", detail.ald_total ?? 0);
                                detailParams.Add("@instock_status", detail.instock_status ?? 0);
                                detailParams.Add("@outstock_status", detail.outstock_status ?? 0);
                                detailParams.Add("@ald_outstockNO_num", detail.ald_outstockNO_num ?? detail.ald_num);
                                detailParams.Add("@ald_instockNO_num", detail.ald_instockNO_num ?? detail.ald_num);
                                detailParams.Add("@remark", detail.remark);

                                await sqlConnection.ExecuteAsync(
                                    detailSql,
                                    detailParams,
                                    transaction: transaction
                                );
                            }
                        }

                        // 提交事务
                        transaction.Commit();
                        return alId;
                    }
                    catch (Exception ex)
                    {
                        // 回滚事务
                        transaction.Rollback();
                        // 记录日志
                        // Logger.Error(ex, "调拨单添加失败");
                        throw new ApplicationException("调拨单添加失败，请检查数据格式或联系管理员", ex);
                    }
                }
            }
        }

        //修改
        public async Task<int> UpdateAudit(Allot_h allot_h, List<Allot_d> allot_ds)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                using (var transaction = sqlConnection.BeginTransaction())
                {
                    try
                    {
                        // 1. 更新主表数据
                        var mainSql = @"
                    UPDATE tr_allot_h 
                    SET 
                        al_type = @al_type,
                        al_date = @al_date,
                        out_wh_id = @out_wh_id,
                        out_wh_name = @out_wh_name,
                        in_wh_id = @in_wh_id,
                        in_wh_name = @in_wh_name,
                        al_user = @al_user,
                        al_categories = @al_categories,
                        al_num = @al_num,
                        al_total = @al_total,
                        al_status = @al_status,
                        al_outstockNO_num = @al_outstockNO_num,
                        al_instockNO_num = @al_instockNO_num,
                        instock_status = @instock_status,
                        outstock_status = @outstock_status,
                        create_name = @create_name,
                        create_time = @create_time,
                        remark = @remark
                    WHERE al_id = @al_id";

                        var mainParams = new DynamicParameters();
                        mainParams.Add("@al_id", allot_h.al_id);
                        mainParams.Add("@al_type", allot_h.al_type);
                        mainParams.Add("@al_date", allot_h.al_date);
                        mainParams.Add("@out_wh_id", allot_h.out_wh_id);
                        mainParams.Add("@out_wh_name", allot_h.out_wh_name);
                        mainParams.Add("@in_wh_id", allot_h.in_wh_id);
                        mainParams.Add("@in_wh_name", allot_h.in_wh_name);
                        mainParams.Add("@al_user", allot_h.al_user);
                        mainParams.Add("@al_categories", allot_h.al_categories);
                        mainParams.Add("@al_num", allot_h.al_num);
                        mainParams.Add("@al_total", allot_h.al_total);
                        mainParams.Add("@al_status", allot_h.al_status ?? 0);
                        mainParams.Add("@al_outstockNO_num", allot_h.al_outstockNO_num ?? allot_h.al_num);
                        mainParams.Add("@al_instockNO_num", allot_h.al_instockNO_num ?? allot_h.al_num);
                        mainParams.Add("@instock_status", allot_h.instock_status ?? 0);
                        mainParams.Add("@outstock_status", allot_h.outstock_status ?? 0);
                        mainParams.Add("@create_name", allot_h.create_name);
                        mainParams.Add("@create_time", allot_h.create_time);
                        mainParams.Add("@remark", allot_h.remark);

                        int rowsAffected = await sqlConnection.ExecuteAsync(
                            mainSql,
                            mainParams,
                            transaction: transaction
                        );

                        if (rowsAffected == 0)
                        {
                            throw new ApplicationException("未找到要更新的调拨单记录");
                        }

                        // 2. 删除旧的从表数据
                        var deleteDetailSql = "DELETE FROM tr_allot_d WHERE al_id = @al_id";
                        var deleteParams = new DynamicParameters();
                        deleteParams.Add("@al_id", allot_h.al_id);
                        await sqlConnection.ExecuteAsync(deleteDetailSql, deleteParams, transaction: transaction);

                        // 3. 插入新的从表数据
                        if (allot_ds != null && allot_ds.Count > 0)
                        {
                            var detailSql = @"
                        INSERT INTO tr_allot_d (
                            al_id,
                            proc_id,
                            proc_name,
                            ald_num,
                            ald_price,
                            ald_total,
                            instock_status,
                            outstock_status,
                            ald_outstockNO_num,
                            ald_instockNO_num,
                            remark
                        ) 
                        VALUES (
                            @al_id,
                            @proc_id,
                            @proc_name,
                            @ald_num,
                            @ald_price,
                            @ald_total,
                            @instock_status,
                            @outstock_status,
                            @ald_outstockNO_num,
                            @ald_instockNO_num,
                            @remark
                        )";

                            foreach (var detail in allot_ds)
                            {
                                var detailParams = new DynamicParameters();
                                detailParams.Add("@al_id", allot_h.al_id);
                                detailParams.Add("@proc_id", detail.proc_id);
                                detailParams.Add("@proc_name", detail.proc_name);
                                detailParams.Add("@ald_num", detail.ald_num);
                                detailParams.Add("@ald_price", detail.ald_price ?? 0);
                                detailParams.Add("@ald_total", detail.ald_total ?? 0);
                                detailParams.Add("@instock_status", detail.instock_status ?? 0);
                                detailParams.Add("@outstock_status", detail.outstock_status ?? 0);
                                detailParams.Add("@ald_outstockNO_num", detail.ald_outstockNO_num ?? detail.ald_num);
                                detailParams.Add("@ald_instockNO_num", detail.ald_instockNO_num ?? detail.ald_num);
                                detailParams.Add("@remark", detail.remark);

                                await sqlConnection.ExecuteAsync(
                                    detailSql,
                                    detailParams,
                                    transaction: transaction
                                );
                            }
                        }

                        if (!allot_h.al_id.HasValue)
                        {
                            throw new ArgumentException("调拨单 ID 不能为空");
                        }

                        // 提交事务
                        transaction.Commit();

                        return allot_h.al_id.Value;
                    }
                    catch (Exception ex)
                    {
                        // 回滚事务
                        transaction.Rollback();
                        throw new ApplicationException("调拨单更新失败，请检查数据格式或联系管理员", ex);
                    }
                }
            }
        }

        public async Task<Allot_h> GetId(int id)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();

                // 构建查询语句，直接包含必要的条件
                string sql = "SELECT * FROM tr_allot_h WHERE  al_id = @id";

                // 创建动态参数
                var parameters = new DynamicParameters();
                parameters.Add("@id", id);

                // 执行查询并返回结果集合
                return await sqlConnection.QueryFirstOrDefaultAsync<Allot_h>(sql, parameters);
            }
        }

        public Task<List<Allot_d>> GetAid(int id)
        {
            throw new NotImplementedException();
        }

        public async Task<AllotTotals> Gettotal(string? proc_code, string? al_code, string? al_type, string? header_create_by, int? type_id, string? proc_primary_unit, string? al_user, int? out_wh_id, int? in_wh_id, int[] instock_status, int[] outstock_status, string? startTime, string? endTime, string? createstartTime, string? createendTime)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();

                string sql = @"
                SELECT 
                    COALESCE(COUNT(DISTINCT vad.al_id), 0) AS TotalRecordsH,
                    COALESCE(COUNT(DISTINCT vad.ald_id), 0) AS TotalRecordsD,
                    COALESCE(SUM(vad.ald_total), 0) AS TotalAmountD -- 建议给金额列一个更清晰的别名
                FROM [dbo].[vw_allotment_details] vad";

                var whereBuilder = new StringBuilder(" WHERE header_is_delete = 0 AND product_is_delete = 0");

                // 添加可选条件（直接拼接值）
                if (!string.IsNullOrEmpty(proc_code))
                {
                    whereBuilder.Append($" AND (product_name LIKE '%{proc_code}%' OR proc_code LIKE '%{proc_code}%')");
                }

                if (!string.IsNullOrEmpty(al_code))
                {
                    whereBuilder.Append($" AND al_code LIKE '%{al_code}%'");
                }

                if (!string.IsNullOrEmpty(al_type))
                {
                    whereBuilder.Append($" AND al_type = '{al_type}'");
                }

                if (!string.IsNullOrEmpty(header_create_by))
                {
                    whereBuilder.Append($" AND header_create_by LIKE '%{header_create_by}%'");
                }

                if (!string.IsNullOrEmpty(proc_primary_unit))
                {
                    whereBuilder.Append($" AND proc_primary_unit = '{proc_primary_unit}'");
                }

                if (!string.IsNullOrEmpty(al_user))
                {
                    whereBuilder.Append($" AND al_user LIKE '%{al_user}%'");
                }

                if (type_id.HasValue)
                {
                    whereBuilder.Append($" AND type_id = {type_id.Value}");
                }

                if (out_wh_id.HasValue)
                {
                    whereBuilder.Append($" AND out_wh_id = {out_wh_id.Value}");
                }

                if (in_wh_id.HasValue)
                {
                    whereBuilder.Append($" AND in_wh_id = {in_wh_id.Value}");
                }

                if (!string.IsNullOrEmpty(startTime) && !string.IsNullOrEmpty(endTime))
                {
                    whereBuilder.Append($" AND al_date BETWEEN '{startTime}' AND '{endTime}'");
                }

                if (!string.IsNullOrEmpty(createstartTime) && !string.IsNullOrEmpty(createendTime))
                {
                    whereBuilder.Append($" AND header_create_time BETWEEN '{createstartTime}' AND '{createendTime}'");
                }

                // 处理 instock_status 和 outstock_status
                if ((instock_status != null && instock_status.Length > 0) ||
                    (outstock_status != null && outstock_status.Length > 0))
                {
                    string orCondition = "";
                    if (instock_status != null && instock_status.Length > 0)
                        orCondition += $"detail_instock_status IN ({string.Join(",", instock_status)})";

                    if (outstock_status != null && outstock_status.Length > 0)
                    {
                        if (orCondition.Length > 0)
                            orCondition += " OR ";
                        orCondition += $"detail_outstock_status IN ({string.Join(",", outstock_status)})";
                    }

                    whereBuilder.Append($" AND ({orCondition})");
                }

                string finalSql = sql + whereBuilder.ToString();

                using (SqlCommand command = new SqlCommand(finalSql, sqlConnection))
                {
                    using (SqlDataReader reader = await command.ExecuteReaderAsync())
                    {
                        if (await reader.ReadAsync())
                        {
                            return new AllotTotals
                            {
                                TotalRecordsH = reader.GetInt32(0),
                                TotalRecordsD = reader.GetInt32(1),
                                TotalSum = reader.GetDecimal(2)  // 现在永远不会是 NULL
                            };
                        }
                        return new AllotTotals();
                    }
                }
            }
        }

        public async Task<List<Allot_h>> GetAllot_Hs(string name)
        {
            using(SqlConnection sqlConnection=new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"select * from [dbo].[tr_allot_h] where is_delete=0 and al_status=9 and {name}=0";
                return sqlConnection.Query<Allot_h>(sql).ToList();
            }
        }

        public async Task<int> Getid(string code)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"select al_id from [dbo].[tr_allot_h] where al_code='{code}'";
                return await sqlConnection.ExecuteScalarAsync<int>(sql);
            }
        }

        public async Task<int> UpdateStatus(string nameok, int oknum, string nameno, int nonum, string name, int alid)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"update [dbo].[tr_allot_h] set {nameok}={oknum},{nameno}={nonum},{name}=2 where al_id={alid}";
                return await sqlConnection.ExecuteAsync(sql);
            }
        }

        public async Task<List<Allot_h>> GetByPid(int procid)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"select h.* from [dbo].[tr_allot_h] h
                                inner join [dbo].[tr_allot_d] d on h.al_id=d.al_id
                                where proc_id={procid}";
                return sqlConnection.Query<Allot_h>(sql).ToList();
            }
        }
    }
}
